Complete Guide to Excel 365: Getting Started

Complete Guide to Excel 365: Getting Started

• discover the key concepts covered in this course
• launch Microsoft Excel, explore available templates, and customize the Quick Access Toolbar
• explore different menu sections within the toolbar ribbon such as Page Layout, Formulas, Data, Review and View, and create a new ribbon tab
• copy-paste data and use the Format Cells right-click menu option to customize number formatting, alignment, font, border, fill, and protection
• insert and delete rows and columns, alter cell fill color and line borders, and merge cells via the Home menu
• apply different currency formats, alter displayed number precision via the decimal point, copy-paste non-contiguous ranges, and rename sheets in an Excel workbook
• use the Text Import Wizard to open text files with delimited columns, and open comma-separated and tab-delimited text files using the Data import menu item
• use the Text Import Wizard to open fixed-width files containing columns of fixed character numbers, create, delete, and move column breaks, and fix data that was incorrectly imported as fixed-width when it was delimited
• recognize Flash Fill as a way to fill in missing data intelligently, import a file as an Excel data table, export workbooks to formats such as PDF/XPS, and identify the potential for data loss when a workbook is saved as a CSV file
• summarize the key concepts covered in this course

Microsoft Excel has many potential applications, offering extensive, customizable features to suit virtually all tasks and workflows. At first, Excel can be intimidating, but the benefits of knowing how to eke out every bit of this tool's productivity far outweigh the slight learning curve.

In this course, you'll learn how to set up Excel, identify and choose an Excel template, locate and recognize the purpose of each primary menu item group in the Quick Access Toolbar, and apply customization to this toolbar. Next, you'll execute basic cell operations, such as copy-pasting data and inserting and deleting rows and columns. You'll and apply a range of cell formatting options, such as alignment, font, and currency formats. Finally, you'll import delimited and fixed-width data and work with Excel's Flash Fill functionality.

Complete Guide to Excel 365: Linking, Printing, & Protecting Workbooks

Complete Guide to Excel 365: Linking, Printing, & Protecting Workbooks

• discover the key concepts covered in this course
• use the Excel Options menu to customize how workbooks are saved, configure AutoSave and AutoRecover behavior and the default save location, and use the Manage Workbook functionality to recover or delete unsaved workbooks
• use built-in templates, save a commonly-used workbook as an Excel Template (.xltx) file, and recognize the purpose of the Custom Office Templates folder, the names "Book" and "Sheet" for .xltx files, and the XLSTART sub-folder
• use the Data Outline menu item to logically group data based on rows and columns, the Ungroup option to undo grouped data, and list the capabilities and limitations of the Auto Outline feature
• create links to external workbooks, use the Paste Link option from the Paste menu, parse the syntax of external link addresses, and recognize the dynamic nature of data linked from external workbooks
• edit external links using the Data menu, use the Open Source, Check Status, Change Source, and Break Link options, and customize the startup prompt when a workbook with external links is opened
• combine the Print and Page Layout menu features to control how workbooks are printed and use Page Break Preview to control pagination, specify collation, scaling, paper type, and orientation
• customize headers and footers, display and hide hidden columns, insert images into workbooks, control whether images are printed and whether they appear within the data or in the header/footer, and set and customize a workbook's background image and transparency
• use the Protect Sheet feature from the Review menu to control how a sheet can be edited, customize which operations are possible without a password, and use the Allow Edit Ranges feature to override protection for specific areas on a worksheet
• control cell-level protection and understand the semantics of the locked and hidden checkboxes, apply workbook-level protection options, such as open in read-only mode, use password encryption, add a digital signature, mark as final, and outline the semantics and limitations of opening in read-only mode
• summarize the key concepts covered in this course

Some of the core benefits of working with data in Excel include connecting data combined from different workbooks and controlling how it's presented and who has access to it. In this course, you'll begin by linking data in different Excel workbooks, before editing, testing, and cleanly breaking those links.

Next, you'll work with an often-ignored but powerful aspect of Excel - precise control over how a workbook is laid out and printed. You'll configure headers, footers, page numbers, background, images, and the views used to print only parts of a workbook.

Finally, you'll use Excel's protection features for individual cells and cell ranges, worksheets, and workbooks. This includes setting roles and passwords, working with read-only workbooks, and utilizing editable cell ranges.

Complete Guide to Excel 365: Pivot, PowerPivot, & Financial Modeling

Complete Guide to Excel 365: Pivot, PowerPivot, & Financial Modeling

• discover the key concepts covered in this course
• import data in relational (tabular) form, create pivot tables by attempting to link multiple tables, and specify issues that can arise if pivot tables are created without accurately capturing relationships  
• auto-detect relationships using the PivotTable feature, and illustrate how foreign key relationships between parent and child tables are detected and dynamically reflected in pivot tables  
• use PowerPivot to detect and manage relationships, create data models, and inter-operate with traditional Excel pivot tables  
• contrast the auto-detection of relationships performed in PowerPivot vs. in traditional pivot tables, visualize foreign-key relationships, and use entity-relationship diagrams in PowerPivot  
• model fixed-income instruments, such as bonds using Excel's financial functions, use the EDATE() function to compute settlement dates, the EFFECT() function to find effective compounding rates, and the FV() function to compute the future value of a stream of cash flows  
• compute the yield of a bond using the YIELD() function, demonstrate the inverse relationship between prices and yields, and use the PV() function to find the present value of a series of cash flows  
• perform NPV (net present value) calculations using both the Excel NPV() function and hand-rolled formulas that take into account the discount rates and coupons of a fixed income instrument  
• summarize the key concepts covered in this course

In this course, you'll recognize how relational data - data with schema and clearly-defined column names - can be imported into Excel as a set of data tables. This data can be sliced-and-diced using classic Excel pivot tables or the more robust PowerPivot add-in. Now a standard part of Excel, this add-in vastly expands the tool's capabilities with some serious entity-relationship modeling and big data analysis.

Excel has powerful capabilities to detect relationships across models and infer foreign key relationships between parent and child tables. Creating, visualizing, and modeling such relationships is an important aspect of working with relational databases. As you'll recognize, you can now accomplish much of that from within Excel using PowerPivot.

In this course, you'll use some classic yet powerful worksheet functions that have ensured Excel's use in Wall Street for decades. These functions can be used to compute the yield of a bond and the present and future values of a set of cash flows. They can also be used to perform complex operations on settlement dates and compounding rates of financial instruments.

Complete Guide to Excel 365: Using Formatting, Styles, & Themes

Complete Guide to Excel 365: Using Formatting, Styles, & Themes

• discover the key concepts covered in this course
• copy-paste formulas with relative cell references, use the Insert Function menu to see built-in worksheet functions, arguments, and results, and use the AutoFill options to control how copy-pasted data is handled
• use keyboard shortcuts to insert line breaks and the date and time into a cell, set default fill behavior when copy-pasting dates and times, customize AutoCorrect options to control workbook proofing, extend built-in AutoCorrect rules to expand acronyms automatically, and work with fractions and percentages  
• use Excel's Comments feature to have threaded conversations, insert, show, hide, and delete notes and comments, and prevent metadata loss when converting notes to comments  
• recognize the #SPILL! error and data tables features, such as named columns, calculated columns, and headers, use data tables to filter, sort, and remove duplicate data, and convert to and from traditional Excel ranges
• use the Conditional Formatting menu item to highlight cells based on rules, use relational operators (greater than, less than, between) and text and date filters, and visualize data properties by highlighting duplicates, top-N, bottom-N, above-average, and below-average values  
• use data bars, color scales, and icon sets to create visually appealing, conditionally formatted spreadsheets, and depict directional data using arrows, shapes, ratings, and OK-Warning-Error indicators  
• create new formatting rules and vary both conditions governed by the rule as well as precise formatting aspects, highlight outliers that lie a certain number of standard deviations from the mean, highlight unique and duplicate values, use the Find & Select menu item to locate all cells governed by conditional formatting rules, and clear conditional formatting rules  
• use formulas to determine which cells will be governed by a conditional formatting rule, the logical OR function to construct a boolean predicate to highlight weekends in a range of dates, and the ROW() function to alter highlighting based on cell location  
• use built-in cell styles and workbook themes, create new styles and themes, and export a theme for use in different MS-Office products
• summarize the key concepts covered in this course

Conditional formatting is a significant and powerful feature in Excel. In this course, you'll begin by using some of the more straightforward types of conditional formattings, such as those based on specific values or ranking. You'll then use the more visually appealing types of conditional formatting, such as data bars, icon sets, and color scales, before advancing to more complex custom formatting rules using worksheet functions and a cell's row and column information.

Additionally, you'll distinguish the purpose of notes versus comments and how to work with them both. Finally, you'll learn to use built-in styles and themes, create custom styles and themes and export them for use in other Office 365 products, such as PowerPoint, to apply a uniform look-and-feel to all your spreadsheets and presentations.

Complete Guide to Excel 365: Validating, Cleaning, & Performing Lookups on Data

Complete Guide to Excel 365: Validating, Cleaning, & Performing Lookups on Data

• discover the key concepts covered in this course
• Use absolute and relative cell references in worksheet formulae, lock cell references using the $ symbol and named ranges, and use the trace error feature to debug formula errors  
• analyze data using the Subtotal feature, specify when the subtotal function should be applied, compute group summaries and grand totals, and remove changes to the workbook made by the Subtotal menu 
• use the Filter feature from the Data menu to focus on specific subsets of data, illustrate how Filter and Subtotal differ in making changes to a workbook; identify and avoid circular references using Excel Error Checking  
• perform conditional analysis using the IF() function, and combine logical conditions using the AND() and OR() functions
• use conditional aggregate functions, such as COUNTIF() and COUNTIFS(), to perform aggregate operations based on specific conditions and recognize the semantics of predicates created from strings for use within these functions  
• utilize VLOOKUP() and HLOOKUP() functions for one-dimensional lookups that join data ranges based on a common column, then use relative cell references for the lookup value and absolute cell references for the lookup range 
• use the INDEX() and MATCH() functions to perform two-dimensional lookups and nest two calls to MATCH() within a single call to INDEX() to match on both row and column  
• implement common data-cleaning operations, such as eliminating duplicates via the Data menu, use CLEAN() to remove unwanted whitespace characters, correctly interpret numbers stored as text, and use CONVERT() for an extensive range of unit conversion operations  
• work with external hyperlinks and parse them using string functions, such as RIGHT(), LEFT(), MID(), SUBSTITUTE() and LEN(), and fill in blank/missing values using the Find & Select menu option and the Go To Special feature  
• specify data validation constraints, configure bounds on acceptable values, create user-friendly error messages, and validate data based on decimal, whole number, text length, and date/time values  
• create lists for use in data validation and use named ranges and the INDIRECT() function to create a robust and dynamic data validation mechanism  
• summarize the key concepts covered in this course

Excel is not only used for aggregating and analyzing data, but also for data cleansing. There are several data cleaning, validation, and checking techniques available, some of which are among Excel's most well-known and widely-used functions.

In this course, you'll start by using various string and data manipulation functions to clean data and fill in missing values.

Next, you'll perform simple data validation based on specific numeric thresholds and text lengths, before moving on to validation using lists. You'll then combine data validation to implement a formidable, dynamic data validation mechanism using named ranges and the INDIRECT formula.

Finally, you'll use one-dimensional lookups - the classic HLOOKUP and VLOOKUP worksheet functions - as well as more complex, two-dimensional lookups utilizing a combination of INDEX and MATCH. 

Complete Guide to Excel 365: What-If Analysis, Solver, & Analysis ToolPak

Complete Guide to Excel 365: What-If Analysis, Solver, & Analysis ToolPak

• discover the key concepts covered in this course
• Use the What-If Analysis feature in Excel, create scenarios based on specific cell values, assign names to these scenarios, and toggle between them, summarize a range of outcomes across scenarios by performing What-If Analysis, and interpret the returned results  
• use Goal Seek for solving simple univariate optimization problems, such as solving univariate equations where a target output cell must be set to a specific value by changing a single input cell
• enable the Solver add-in and use it to perform complex multi-variable optimization, specify objective functions to min/max or specific values, and specify non-negativity conditions and solution methods for contraints
• perform simple statistical analysis of two-dimensional data, compute measures of central tendency (mean, median, mode) and dispersion (standard deviation and variance), define Bessel's correction, and calculate skew and kurtosis  
• enable Analysis ToolPak and use it to compute correlation and covariance matrices, interpret the results, and recognize the link between covariance and variance
• implement hypothesis testing using the Analysis ToolPak, perform the two-sample F-test for variance equality and two-sample t-test for equality of means, and interpret the significance level (alpha), test statistic, and p-value
• implement ANOVA (Analysis of Variance) using Analysis ToolPak to analyze variances within and between groups  
• use Analysis ToolPak for histogram analysis and descriptive statistic computing, compute ranks and percentiles, and perform both random and periodic sampling  
• generate random numbers drawn from various distributions using Analysis ToolPak, apply normal, Bernoulli, Poisson, and other distributions, specify population parameters, such as mean and variance, and recognize why sample mean and variance might differ from them  
• perform linear regression using Analysis ToolPak, interpret the results of regression including R-square, p-values, and t-statistics of individual regression coefficients, and identify the benefits of using Analysis ToolPak over worksheet functions such as LINEST(),SLOPE(), and INTERCEPT()  
• summarize the key concepts covered in this course

Excel's Scenario Manager allows you to create named scenarios with different values for key variables. These scenarios can be quickly examined and summarized using the What-If Analysis tool.

In this course, you'll use GoalSeek and Solver, both of which are mathematical tools. Goal Seek comes in handy when solving quadratic, cubic, or other equations. Solver is much more powerful and allows complex constrained optimization problems to be easily defined and addressed.

As you'll recognize, Solver's interface, used for specifying objective functions and constraints, is intuitive and easy to use.

Next, you'll use several heavy-duty statistical techniques in Analysis ToolPak. These range from the creation of correlation and covariance matrices, hypothesis testing, and F-test and T-test interpretations to ANOVA, random and periodic sampling, and the construction and analysis of linear regression models.

Complete Guide to Excel 365: Working With Charts & Sparklines

Complete Guide to Excel 365: Working With Charts & Sparklines

• discover the key concepts covered in this course
• insert charts and customize chart elements, such as the axes, title, data labels, data callouts, and grid lines, and add analytical elements based on standard deviation, percentage, and standard error, such as trendlines and error bars
• use the chart design item from the toolbar ribbon to change chart layout and color, switch rows and columns, change selected data and chart type, and control whether #N/As and empty cells are displayed as gaps or zeros
• create charts that occupy an entire worksheet and control their appearance using worksheet functions, plot pie charts and alter transparency, glow, and other visual aspects, explode and re-orient sections of pie charts, and work with treemaps and sunburst charts
• use histograms to summarize the statistical properties of data, bin data by category, bin width, or fixed number of bins, perform automatic binning, and set overflow and underflow bins
• perform univariate EDA (exploratory data analysis), use box-and-whisker plots to plot the mean, median, inter-quartile range and outliers, and choose between inclusive and exclusive median methods in quartile calculation
• perform bivariate EDA (exploratory data analysis), use scatter plots to examine relationships between two numeric data columns and the Chart Design menu to create linear, exponential, and other types of trendlines, and identify positively and negatively trending data
• plot geographical data such as countries, states, counties, or postal codes using the Map chart type, use other chart types such 2-D and 3-D column and area charts, and combine multiple visualizations into a single chart using a combo chart type
• construct at-a-glance visualizations using sparklines to build lightweight charts without axes or units contained within individual cells, and use named ranges to control data displayed in a sparkline visualization
• contrast Line and Column sparklines, control the appearance of high, low, and negative points, apply custom formatting to first and last points, display data in reverse order, and use the Quick Access Toolbar to view undoable actions
• summarize the key concepts covered in this course

One of the primary purposes of using Excel is to analyze and present data in a focused, accessible, and accurate way. A great way to do this is with charts and sparklines.

In this course, you'll create basic chart types, such as line, bar, and pie charts, as well as more advanced charts, like treemaps and sunburst charts. In doing this, you'll work with the different aspects of Excel's chart customization capabilities, from chart element transparency to the orientation and exploding out of slices in a pie chart.

Next, you'll advance to use more statistically-oriented chart types, including histograms, scatter plots, and box-and-whisker plots.

Finally, you'll learn to create, customize, and work with Excel's sparklines, which are lightweight visualizations usually contained within a single cell.